--    Program: 		CAPAREXT_AUTO_ACCOUNTING.sql
--    Description:	Extract Auto Accounting for the sob that the user specified 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
  l_count				   	number;
  l_processed_count_1 		number;
  l_rejected_count_1 		number;
  l_processed_count_2		number;
  l_rejected_count_2	   	number;
  l_err_msg       			varchar2(2000);
  
  l_utl_file_1  	   		utl_file.file_type;
  l_utl_file_2 				utl_file.file_type;

  l_filename_1 				varchar2(255);
  l_filename_2 				varchar2(255);

  l_pathname  		   		varchar2(100);

  l_ngo_sob			   		varchar2(20);
  l_ngo_sob_id      		number;
  
  cursor cur_account (p_set_of_books_id varchar2) is
	select rad.*
    from   RA_ACCOUNT_DEFAULTS_ALL rad,
         hr_organization_information hoi
    where  hoi.org_information_context = 'Operating Unit Information'
    and    hoi.org_information3 = p_set_of_books_id
    and    hoi.organization_id = rad.org_id ;
	
  cursor cur_segment (set_gl_default_id number) is
	select * 
	from   ra_account_default_segments
	where  gl_default_id = set_gl_default_id;
	
begin
  l_pathname := '/tmp';
  l_filename_1 := 'CAPAR_AUTO_ACCOUNTING.dat';
  l_filename_2 := 'CAPAR_AUTO_ACCOUNTING_SEGMENT.dat';
  l_utl_file_1 := utl_file.fopen(l_pathname, l_filename_1, 'w', 32767);
  l_utl_file_2 := utl_file.fopen(l_pathname, l_filename_2, 'w', 32767);
  
  l_count := 0;
  l_processed_count_1 := 0;
  l_rejected_count_1  := 0;
  l_processed_count_2 := 0;
  l_rejected_count_2  := 0;
                                        
  l_ngo_sob := '&1';
  
  begin
    select set_of_books_id 
    into l_ngo_sob_id
    from gl_sets_of_books 
    where upper(short_name) = upper(l_ngo_sob);
  exception
     when others then
	    dbms_output.put_line(l_ngo_sob||': set of books name not found');
  end;       
	     
  for s in cur_account(l_ngo_sob_id) loop

    utl_file.put_line( l_utl_file_1, 
      s.gl_default_id || chr(9) ||
      s.LAST_UPDATE_DATE || chr(9) ||          
      s.last_updated_by|| chr(9) ||
      s.CREATION_DATE || chr(9) ||
      s.created_by|| chr(9) ||
      s.LAST_UPDATE_LOGIN|| chr(9) ||
      s.TYPE || chr(9) ||
      s.ATTRIBUTE_CATEGORY|| chr(9) ||
      s.ATTRIBUTE1|| chr(9) ||
      s.ATTRIBUTE2|| chr(9) ||
      s.ATTRIBUTE3|| chr(9) ||
      s.ATTRIBUTE4|| chr(9) ||
      s.ATTRIBUTE5|| chr(9) ||
      s.ATTRIBUTE6|| chr(9) ||
      s.ATTRIBUTE7|| chr(9) ||
      s.ATTRIBUTE8|| chr(9) ||
      s.ATTRIBUTE9|| chr(9) ||
      s.ATTRIBUTE10|| chr(9) ||
      s.ATTRIBUTE11|| chr(9) ||
      s.ATTRIBUTE12|| chr(9) ||
      s.ATTRIBUTE13|| chr(9) ||
      s.ATTRIBUTE14|| chr(9) ||
      s.ATTRIBUTE15|| chr(9) ||
      s.ORG_ID     
    );
    
    l_processed_count_1 := l_processed_count_1 + 1; 
        
    for cur in cur_segment (s.gl_default_id) loop  
	  
      utl_file.put_line( l_utl_file_2, 
        cur.GL_DEFAULT_SEGMENT_ID|| chr(9) ||
        cur.LAST_UPDATE_DATE || chr(9) ||          
        cur.last_updated_by|| chr(9) ||
        cur.CREATION_DATE || chr(9) ||
        cur.created_by|| chr(9) ||
        cur.LAST_UPDATE_LOGIN|| chr(9) ||
        cur.GL_DEFAULT_ID|| chr(9) ||
        cur.SEGMENT|| chr(9) ||
        cur.SEGMENT_NUM|| chr(9) ||
        cur.TABLE_NAME || chr(9) ||
		cur.CONSTANT|| chr(9) ||
        cur.ATTRIBUTE_CATEGORY|| chr(9) ||          
        cur.ATTRIBUTE1 || chr(9) ||
        cur.ATTRIBUTE2 || chr(9) ||
        cur.ATTRIBUTE3|| chr(9) ||
        cur.ATTRIBUTE4|| chr(9) ||
        cur.ATTRIBUTE5|| chr(9) ||
        cur.ATTRIBUTE6|| chr(9) ||
        cur.ATTRIBUTE7|| chr(9) ||
        cur.ATTRIBUTE8|| chr(9) ||
		cur.ATTRIBUTE9|| chr(9) ||
        cur.ATTRIBUTE10|| chr(9) ||          
        cur.ATTRIBUTE11|| chr(9) ||
        cur.ATTRIBUTE12|| chr(9) ||
        cur.ATTRIBUTE13|| chr(9) ||
        cur.ATTRIBUTE14|| chr(9) ||
        cur.ATTRIBUTE15||chr(10)
      );
    
      l_processed_count_2 := l_processed_count_2 + 1;      
      
    end loop;                
  end loop;
  
  l_err_msg := '';  
  if l_processed_count_1 = 0 then
    l_rejected_count_1 := l_rejected_count_1 + 1;
    l_err_msg := l_ngo_sob||': Auto Accounting not found';
  end if;
  
  insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ( 'CAPAREXT_AUTO_ACCOUNTINT', sysdate, l_processed_count_1, l_rejected_count_1, l_processed_count_1-l_rejected_count_1, l_err_msg );
  
  l_err_msg := '';  
  if l_processed_count_2 = 0 then
    l_rejected_count_2 := l_rejected_count_2 + 1;
    l_err_msg := l_ngo_sob||': Auto Accounting Segment not found';
  end if;
  
  insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ( 'CAPAREXT_AUTO_ACCOUNTINT_SEGMENT', sysdate, l_processed_count_2, l_rejected_count_2, l_processed_count_2-l_rejected_count_2, l_err_msg );
          
  commit;
  
  utl_file.fclose(l_utl_file_1);
  utl_file.fclose(l_utl_file_2);
 
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;
end;
/


--exit
